Pyramid determines the table relationships (joins) based on heuristics that define the first column in each table as the primary key column. It then finds the primary key column in each table and looks for tables that have the same column as a foreign key. The algorithm used to create joins can be changed or disabled if needed.
Join Types
- Full Outer Join: Returns all rows from both tables.
- Inner Join: Returns all rows from both tables based on a matching column. Only returns rows where the matching column's values are identical.
- Left Outer Join: Returns all rows from the first/left table, and matching rows from the second/right table.
- Right Outer Join: Returns all rows from the second/right table, and matching rows from the first/left table.
How Joins are Defined
Pyramid uses heuristics to determine the table relationships (joins) in the data model. There are three different algorithms (called 'auto-relationships') to choose from; the default algorithm defines the first column in each table as the primary key column. It then finds the primary key column in each table, and looks for tables that have the same column as a foreign key. Joins are created from the tables containing the primary key column, to any tables containing that same column as a foreign key.
If the default algorithm doesn't suit your data model, you can choose a different auto-relationship to create joins based on table or column names. Alternatively, you can disable auto-relationships and add all the joins manually.
Note: By definition, primary key columns contain unique values; each primary key column row contains a unique identifier. If a column that was set as a primary key contains any duplicate rows, you will receive a warning after the model is processed.
- Click here to learn more about auto-relationships.
Adding and Editing Joins
New joins can be added from a column in one table, to a column in another table. Joins can be edited from the Join context menus, and from a selected join's Properties panel.
Editing joins can involve changing the join type, changing or adding join key columns, setting a join key condition, changing the join key operation, the join direction, or making a join bidirectional.
- Click here to learn about join keys.
Editing Relationships
Table relationships can be edited by changing existing joins, adding new joins, or deleting joins.
Change Join Type
Click the existing Join (purple arrow below) to open both the join type context menu (orange arrow) and the Properties panel (green box). You can change the Join Type from either location.
Add Joins
Add a join by right-clicking the Column Name belonging to the owner side (purple arrow) and then, from Add Relationship, selecting the target table and column that you want the Join to connect to.
Context Menu
Right-click a Join to delete it, switch its direction, or validate it:
Delete Joins
The join can be deleted by right-clicking the Join and clicking Delete from the context menu (red highlight above).
Switch Direction
The join direction can be changed or "switched" for any join:
- Right-click the Join on the canvas to select Switch Direction from its context menu (red highlight above).
- Select the join to open its Properties panel and, from there, click the Switch Direction button (yellow arrow below).
Validate Join
Ensure that the join relationship is valid:
- Right-click the Join to open its context menu and click Validate (red highlight below).
- Select the join to open its Properties panel and click the Validate button (orange highlight below).
If the relationship is valid, you'll receive a green confirmation message. If the relationship is not valid, you'll receive an orange alert with an explanation of the issues found.
Tip: You can also select Validate Joins on the ribbon to validate the joins on the diagram.
- Click here to learn more about validating joins.
Join Properties
Click the existing Join to open its Properties panel:
Join Type
Change the join type from the Join Type drop-down list (green arrow above).
Switch Direction
Change the join direction by clicking the Switch Direction button (yellow arrow above).
Add or Edit Join Keys
- Add join keys by clicking New Join Key (pale blue arrow above) and then selecting the required columns.
- If needed, change the join key columns using the drop-downs (purple highlight above).
- Change the join key operator (white arrow above).
- Click here to learn more about join keys.
Multiple Join Keys
Where there are multiple Join Keys, the following options are available:
- Add an And/Or condition between the Join Keys (orange arrow above).
- If there is more than one Join Key, you can delete all but one of them using the Delete option that is shown on hover (purple arrow above).
Bidirectional
Joins can be one-way or two-way (bidirectional). The most common type is one-way, and this is the default in Pyramid. You can make a bidirectional join by selecting the Bidirectional checkbox (brown arrow above).
- Click here to learn about bidirectional joins.
Many To Many
When the relationship between the primary and foreign tables is "one-to-many," and it doesn't validate due to duplications of unique keys, the relationship will be replaced with a many-to-many relation and as a result will be a valid connection (the user should understand his data and understand that it can be a many to many relationships). The Many To Many checkbox is dark blue on the preceding screen shot.
Copy the join script
Click Copy (green arrow above) to copy the script that underlies this join to your clipboard. Tip: You can use this script when testing in the database.